from datetime import datetime, timezone
from werkzeug.security import generate_password_hash, check_password_hash

from extensions import db
from sqlalchemy.sql import func


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False, index=True)
    username = db.Column(db.String(80), unique=True, nullable=False, index=True)
    password_hash = db.Column(db.String(128), nullable=False)
    role = db.Column(db.String(20), default='user')  # 可设置为 'admin' 或 'user'
    is_deleted = db.Column(db.Boolean, default=False)
    is_system = db.Column(db.Boolean, default=False, comment='是否系统预置:True,False')
    created_at = db.Column(db.DateTime, default=func.now())
    updated_at = db.Column(db.DateTime, default=func.now(), onupdate=func.now())

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return f'<User {self.username}>'

    def to_dict(self):
        return {
            'id': self.id,
            'email': self.email,
            'username': self.username,
            'role': self.role,
            'is_deleted': self.is_deleted,
            # yyyy-mm-dd HH:MM:SS
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    description = db.Column(db.Text)
    is_deleted = db.Column(db.Boolean, default=False)
    is_system = db.Column(db.Boolean, default=False, comment='是否系统预置:True,False')
    created_at = db.Column(db.DateTime, default=func.now())
    updated_at = db.Column(db.DateTime, default=func.now(), onupdate=func.now())

    def __repr__(self):
        return f'<Category {self.name}>'

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'is_deleted': self.is_deleted,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class Website(db.Model):
    __tablename__ = 'websites'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150), nullable=False)
    url = db.Column(db.String(255), nullable=False, unique=True, index=True)
    description = db.Column(db.Text, nullable=False)
    logo = db.Column(db.String(255))
    local_logo = db.Column(db.String(255)) # 新增网站时上传或链接的图片下载到本地upload文件夹中，命名img_+时间戳.png
    is_deleted = db.Column(db.Boolean, default=False)
    status = db.Column(db.String(20), default='active') #  可选值：active, inactive
    is_system = db.Column(db.Boolean, default=False, comment='是否系统预置:True,False')
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=func.now())
    updated_at = db.Column(db.DateTime, default=func.now(), onupdate=func.now())
    category = db.relationship('Category', backref='websites')

    def __repr__(self):
        return f'<Website {self.name} - {self.url}>'

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'url': self.url,
            'description': self.description,
            'logo': self.logo,
            'local_logo': self.local_logo,
            'is_deleted': self.is_deleted,
            'status': self.status,
            'category_id': self.category_id,
            'category': {
                'id': self.category.id if self.category else None,
                'name': self.category.name if self.category else None
            },
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }


class Suggestion(db.Model):
    __tablename__ = 'suggestions'
    id = db.Column(db.Integer, primary_key=True)
    ip = db.Column(db.String(45), nullable=False, index=True)
    email = db.Column(db.String(120), index=True)
    site_name = db.Column(db.String(150), nullable=False)
    site_url = db.Column(db.String(255), nullable=False)
    site_description = db.Column(db.Text, nullable=False)
    site_logo = db.Column(db.String(255))
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    created_at = db.Column(db.DateTime, default=func.now(), index=True)
    updated_at = db.Column(db.DateTime, default=func.now(), onupdate=func.now())
    handled = db.Column(db.Boolean, default=False, index=True)

    def __repr__(self):
        return f'<Suggestion {self.site_name} - {self.site_url}>'

    def to_dict(self):
        return {
            'id': self.id,
            'ip': self.ip,
            'email': self.email,
            'site_name': self.site_name,
            'site_url': self.site_url,
            'site_description': self.site_description if self.site_description else None,
            'site_logo': self.site_logo,
            'category_id': self.category_id,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat() if self.updated_at else None,
            'handled': self.handled,
        }


class AccessLog(db.Model):
    __tablename__ = 'access_logs'
    id = db.Column(db.Integer, primary_key=True)
    ip = db.Column(db.String(45), nullable=False, index=True)
    original_ip = db.Column(db.String(45))  # ✅ 新增字段
    source = db.Column(db.String(255))  # 原始地址，如公网省市区，或为空
    country = db.Column(db.String(64))
    province = db.Column(db.String(64))
    city = db.Column(db.String(64))
    path = db.Column(db.String(255))  # 访问路径
    created_at = db.Column(db.DateTime, default=func.now(), index=True)

    def __repr__(self):
        return f'<AccessLog {self.ip} {self.path}>'

    def to_dict(self):
        return {
            'id': self.id,
            'ip': self.ip,
            'original_ip': self.original_ip,
            'source': self.source,
            'country': self.country,
            'province': self.province,
            'city': self.city,
            'path': self.path,
            'created_at': self.created_at.isoformat()
        }

class Image(db.Model):
    __tablename__ = 'images'
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(255), nullable=False, comment='文件名')
    filepath = db.Column(db.String(255), nullable=False, comment='文件路径')
    source_url = db.Column(db.String(500), comment='下载来源链接（如果是下载）')
    upload_ip = db.Column(db.String(45), comment='上传者 IP（如果是上传）')
    created_at = db.Column(db.DateTime, default=func.now(), index=True, comment='上传或下载时间')

    def __repr__(self):
        return f'<Image {self.filename}>'

    def to_dict(self):
        return {
            'id': self.id,
            'filename': self.filename,
            'filepath': self.filepath,
            'source_url': self.source_url,
            'upload_ip': self.upload_ip,
            'created_at': self.created_at.isoformat()
        }

class Settings(db.Model):
    __tablename__ = 'settings'
    id = db.Column(db.Integer, primary_key=True)
    key = db.Column(db.String(255), nullable=False, unique=True, comment='设置项的键')
    value = db.Column(db.Text, comment='设置项的值')
    group = db.Column(db.String(255), comment='设置项的分组:1、网站设置，2、邮箱设置')
    description = db.Column(db.Text, comment='设置项的描述')
    is_deleted = db.Column(db.Boolean, default=False, comment='是否删除')
    is_system = db.Column(db.Boolean, default=False, comment='是否系统预置:True,False')
    is_visible = db.Column(db.Boolean, default=True, comment='是否可见')
    created_by = db.Column(db.String(255), comment='创建人')
    updated_by = db.Column(db.String(255), comment='更新人')

    created_at = db.Column(db.DateTime, default=func.now(), index=True, comment='创建时间')
    updated_at = db.Column(db.DateTime, default=func.now(), onupdate=func.now(), comment='更新时间')
    def __repr__(self):
        return f'<Settings {self.key}: {self.value}>'
    def to_dict(self):
        return {
            'id': self.id,
            'key': self.key,
            'value': self.value,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }